﻿//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//  LIBRERIA: SIMA.CORE                                                                                                 //
//  DESARROLLADO POR JUAN CARLOS VEGA NEIRA                                                                             //
//  UNIVERSIDAD RICARDO PALMA                                                                                           //
//  CURSO DE TALLER DE GERENTCIA DE PROYECTOS                                                                           //
//  SEMESTRE 2010 - II                                                                                                  //
//  SEPTIEMBRE, 2010                                                                                                    //
//  TODOS LOS DERECHOS RESERVADOS                                                                                       //
//  VERSIÓN v1.0                                                                                                        //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SIMA.Generator
{
    internal static class SqlAccessHelper
    {
        internal static string SqlConnectionString(string servername, string username, string password)
        {
            if (username.Length > 0)
            {
                return "Server=" + servername + "; DataBase=master;Integrated Security=SSPI;";
            }
            else
            {
                return "Server=" + servername + "; DataBase=master;Integrated Security=SSPI; User ID=" + username + "; Password=" + password + ";";
            }
        }
        internal static string SqlConnectionString(string servername, string username, string password, string databaseName)
        {
            if (username.Length > 0)
            {
                return "Server=" + servername + "; DataBase=" + databaseName + ";Integrated Security=SSPI;";
            }
            else
            {
                return "Server=" + servername + "; DataBase=" + databaseName + ";Integrated Security=SSPI; User ID=" + username + "; Password=" + password + ";";
            }
        }
    }

    public class SqlAccess
    {
        SqlConnection _connection;
        string server;
        string username;
        string password;
        public SqlAccess(string servername, string username, string password)
        {
            _connection = new SqlConnection(SqlAccessHelper.SqlConnectionString(servername, username, password));
            this.username = username;
            this.password = password;
            this.server = servername;
        }

        public List<DataBase> GetDataBases()
        {
            SqlCommand _command = new SqlCommand("sp_databases", _connection);
            _command.CommandType = CommandType.StoredProcedure;
            List<DataBase> _databases = new List<DataBase>();
            _command.Connection.Open();
            SqlDataReader _reader = _command.ExecuteReader();
            while (_reader.Read())
            {
                DataBase _database = new DataBase();
                _database.Database_name = _reader.GetValue(_reader.GetOrdinal("DATABASE_NAME")).ToString();
                _database.Database_size = Convert.ToDouble(_reader.GetValue(_reader.GetOrdinal("DATABASE_SIZE")).ToString());
                _databases.Add(_database);
            }
            _command.Connection.Close();
            return _databases;
        }

        public List<Table> GetTablesFromDataBase(DataBase _database)
        {
            SqlCommand _command = new SqlCommand("SELECT TABLE_CATALOG,	TABLE_SCHEMA,	TABLE_NAME,	TABLE_TYPE FROM	INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'dtProperties'	AND TABLE_CATALOG = '" + _database.Database_name + "' ORDER BY TABLE_NAME", new SqlConnection(SqlAccessHelper.SqlConnectionString(server, username, password, _database.Database_name)));
            List<Table> _tables = new List<Table>();
            _command.Connection.Open();
            SqlDataReader _reader = _command.ExecuteReader();
            while (_reader.Read())
            {
                Table _table = new Table();
                _table.Name = _reader.GetValue(_reader.GetOrdinal("TABLE_NAME")).ToString();
                _table.Columns = GetColumnFromTable(_database, _table);
                _tables.Add(_table);
                foreach (Column col in GetPrimaryKeys(_database, _table))
                {
                    foreach (Column c in _table.Columns)
                    {
                        if (col.Name == c.Name)
                        {
                            _table.PrimaryKeys.Add(c);
                        }
                    }
                }
                Dictionary<string, Column> _fk = GetForeignKey(_database, _table);

            }
            _command.Connection.Close();
            return _tables;
        }

        public List<Column> GetColumnFromTable(DataBase _database, Table table)
        {
            SqlCommand _command = new SqlCommand(string.Format("SELECT INFORMATION_SCHEMA.COLUMNS.*,COL_LENGTH('{0}', INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME) AS COLUMN_LENGTH,COLUMNPROPERTY(OBJECT_ID('{0}'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed') AS IS_COMPUTED,COLUMNPROPERTY(OBJECT_ID('{0}'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY,COLUMNPROPERTY(OBJECT_ID('{0}'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol') AS IS_ROWGUIDCOL FROM	INFORMATION_SCHEMA.COLUMNS WHERE	INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '{0}'", table.Name), new SqlConnection(SqlAccessHelper.SqlConnectionString(server, username, password, _database.Database_name)));
            List<Column> _columns = new List<Column>();
            _command.Connection.Open();
            SqlDataReader _reader = _command.ExecuteReader();
            while (_reader.Read())
            {
                Column _column = new Column();
                _column.Name = Convert.ToString(_reader.GetValue(_reader.GetOrdinal("COLUMN_NAME")));
                _column.IsComputed = Convert.ToBoolean(_reader.GetValue(_reader.GetOrdinal("IS_COMPUTED")));
                _column.IsIdentity = Convert.ToBoolean(_reader.GetValue(_reader.GetOrdinal("IS_IDENTITY")));
                _column.IsRowGuidCol = Convert.ToBoolean(_reader.GetValue(_reader.GetOrdinal("IS_ROWGUIDCOL")));
                _column.Length = Convert.ToString(_reader.GetValue(_reader.GetOrdinal("COLUMN_LENGTH")));
                _column.Precision = Convert.ToString(_reader.GetValue(_reader.GetOrdinal("NUMERIC_PRECISION")));
                _column.Scale = Convert.ToString(_reader.GetValue(_reader.GetOrdinal("NUMERIC_SCALE")));
                _column.Type = Convert.ToString(_reader.GetValue(_reader.GetOrdinal("DATA_TYPE")));
                _columns.Add(_column);
            }
            _command.Connection.Close();
            return _columns;

        }

        internal static string GetCsType(Column column)
        {
            switch (column.Type.ToLower())
            {
                case "binary":
                    return "byte[]";
                case "bigint":
                    return "long";
                case "bit":
                    return "bool";
                case "char":
                    return "string";
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "decimal";
                case "float":
                    return "float";
                case "image":
                    return "byte[]";
                case "int":
                    return "int";
                case "money":
                    return "decimal";
                case "nchar":
                    return "string";
                case "ntext":
                    return "string";
                case "nvarchar":
                    return "string";
                case "numeric":
                    return "decimal";
                case "real":
                    return "decimal";
                case "smalldatetime":
                    return "DateTime";
                case "smallint":
                    return "short";
                case "smallmoney":
                    return "float";
                case "sql_variant":
                    return "byte[]";
                case "sysname":
                    return "string";
                case "text":
                    return "string";
                case "timestamp":
                    return "DateTime";
                case "tinyint":
                    return "byte";
                case "varbinary":
                    return "byte[]";
                case "varchar":
                    return "string";
                case "uniqueidentifier":
                    return "Guid";
                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
            }
        }

        public List<Column> GetPrimaryKeys(DataBase _database, Table table)
        {
            List<Column> _primaryKeyList = new List<Column>();
            SqlCommand _command = new SqlCommand("sp_pkeys", new SqlConnection(SqlAccessHelper.SqlConnectionString(server, username, password, _database.Database_name)));
            _command.CommandType = CommandType.StoredProcedure;
            _command.Parameters.AddWithValue("@table_name", table.Name);
            _command.Connection.Open();
            SqlDataReader _reader = _command.ExecuteReader();
            while (_reader.Read())
            {
                Column _column = new Column();
                _column.Name = _reader.GetValue(_reader.GetOrdinal("COLUMN_NAME")).ToString();
                _primaryKeyList.Add(_column);
            }
            _command.Connection.Close();
            return _primaryKeyList;
        }
       
        public Dictionary<string, Column> GetForeignKey(DataBase _database, Table table)
        {
            Dictionary<string, Column> foreignKeyList = new Dictionary<string, Column>();
            SqlCommand _command = new SqlCommand("sp_fkeys", new SqlConnection(SqlAccessHelper.SqlConnectionString(server, username, password, _database.Database_name)));
            _command.CommandType = CommandType.StoredProcedure;
            _command.Parameters.AddWithValue("fktable_name", table.Name);
            _command.Connection.Open();
            SqlDataReader _reader = _command.ExecuteReader();
            while (_reader.Read())
            {
                string name = _reader.GetValue(_reader.GetOrdinal("FK_NAME")).ToString();
                string columnName = _reader.GetValue(_reader.GetOrdinal("FKCOLUMN_NAME")).ToString();
            }
            _command.Connection.Close();
            return foreignKeyList;
        }

    }

    public class DataBase
    {
        string database_name;


        public string Database_name
        {
            get { return database_name; }
            set { database_name = value; }
        }
        double database_size;


        public double Database_size
        {
            get { return database_size; }
            set { database_size = value; }
        }
    }
}
